﻿using System;
using System.Configuration;
using System.Data;

using MySql.Data.MySqlClient;



namespace StagingAreaETL
{
    internal static class DBHelper
    {
        /// <summary>
        /// Get string representation of given value valid for SQL sentence.
        /// </summary>
        /// <returns>Returns 'NULL' if the value is null.</returns>
        internal static string toString(object value)
        {
            if (value is bool)
                return (bool) value ? "1" : "0";
            return value == null ? "NULL" : value.ToString();
        }

        /// <summary>
        /// Get open connection to the Operational Data Store.
        /// </summary>
        /// <returns></returns>
        internal static MySqlConnection getODSConnection()
        {
            string myConString = ConfigurationManager.ConnectionStrings["ODSConnectionString"].ConnectionString;
            MySqlConnection connection = new MySqlConnection(myConString);
            connection.Open();
            return connection;
        }

        /// <summary>
        /// Get open connection to the StagingArea.
        /// </summary>
        /// <returns></returns>
        internal static MySqlConnection getSAConnection()
        {
            string myConString = ConfigurationManager.ConnectionStrings["StagingAreaConnectionString"].ConnectionString;
            MySqlConnection connection = new MySqlConnection(myConString);
            connection.Open();
            return connection;
        }


        /// <summary>
        /// Get last generated auto-increment ID. Applies for last INSERT on any table.
        /// </summary>
        /// <param name="conn">Open connection to MySQL server. Will not be closed in this method.</param>
        internal static int getLastID(MySqlConnection conn)
        {
            if (conn.State != ConnectionState.Open)
                throw new ArgumentException("The given connection is in unexpected state (should be open)");
            int result = -1;

            MySqlCommand command = conn.CreateCommand();
            command.CommandText = "SELECT LAST_INSERT_ID()";
            MySqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
                result = reader.GetInt32(0);
            reader.Close();
            return result;
        }


        /// <summary>
        /// Transports data from a table in Staging Area to another in Operational Data Store.
        /// Can be used only when appropriate data for ODS are already ready in SA.
        /// Structures of those tables must be corresponding.
        /// </summary>
        internal static void CarryOverTableData(string srcTable, string dstTable)
        {
            MySqlConnection connection = getSAConnection();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "insert into `ods`.`" + dstTable + "` " +
                                  " select * from " + srcTable + ";";
            command.ExecuteNonQuery();

            connection.Close();
        }
    }
}
